package fr.uniscol.siecle.dao.impl;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import fr.uniscol.dao.UniscolDaoSupport;
import fr.uniscol.model.BaseAnneeScolaire;
import fr.uniscol.siecle.dao.IDonneesSIECLEStructuresDao;
import fr.uniscol.siecle.dao.IImportDonneesSIECLEDao;
import fr.uniscol.siecle.dao.mapper.DivisionRowMapper;
import fr.uniscol.siecle.dao.mapper.GroupeRowMapper;
import fr.uniscol.siecle.model.Division;
import fr.uniscol.siecle.model.FichierSIECLE;
import fr.uniscol.siecle.model.Groupe;
import fr.uniscol.siecle.model.MEF;
import fr.uniscol.siecle.model.Structures;
import static fr.uniscol.dao.impl.SQLNames.*;
import fr.uniscol.model.RapportTraitement;
import org.apache.log4j.Logger;

@Repository("donneesStructuresDao")
public class DonneesSIECLEStructuresDaoImpl extends UniscolDaoSupport implements
		IDonneesSIECLEStructuresDao, IImportDonneesSIECLEDao {

    public static Logger log = Logger.getLogger(DonneesSIECLEStructuresDaoImpl.class);
    
	@Override
	public Structures getDonneesStructures(int annee, long etabId) {
		
		Structures structures = new Structures();
		
		// Classes
		List<Division> divisions = getJdbcTemplate().query("SELECT * FROM bee_division WHERE etablissementid=? AND annee=?", new Object[]{
				etabId, 
				annee
		}, new DivisionRowMapper());
		
		// Groupes
		List<Groupe> groupes = getJdbcTemplate().query("SELECT * FROM bee_groupe WHERE etablissementid=? AND annee=?", new Object[]{
			etabId,
			annee
		}, new GroupeRowMapper());
		
		// TODO Associations
		
		structures.setDivisions(divisions);
		structures.setGroupes(groupes);
		
		return structures;
	}

	@Override
	public boolean saveFichierSIECLE(BaseAnneeScolaire base, FichierSIECLE fichier, RapportTraitement rapport) {
		
		Structures structures = (Structures) fichier;
		
		/*getJdbcTemplate().update("DELETE FROM bee_division_groupe WHERE annee=? AND etablissementid=?", new Object[]{
			base.getAnnee(), 
			base.getEtablissement().getId()
		});
		getJdbcTemplate().update("DELETE FROM bee_structure_mef WHERE annee=? AND etablissementid=?", new Object[]{
			base.getAnnee(), 
			base.getEtablissement().getId()
		});
		getJdbcTemplate().update("DELETE FROM bee_groupe WHERE annee=? AND etablissementid=?", new Object[]{
			base.getAnnee(), 
			base.getEtablissement().getId()
		});
		getJdbcTemplate().update("DELETE FROM bee_division WHERE annee=? AND etablissementid=?", new Object[]{
			base.getAnnee(), 
			base.getEtablissement().getId()
		});
		
		String reqUpdate = "UPDATE bee_structures SET date_export=?, horodatage=? WHERE annee=? AND etablissementid=? ";
		String reqInsert = "INSERT INTO bee_structures (annee, etablissementid, date_export, horodatage) "
				+ "			VALUES(?, ?, ?, ?)";
		
		int nbRowsAffected = getJdbcTemplate().update(reqUpdate, new Object[]{
				structures.getParametres().getDateExport(),
				structures.getParametres().getHorodatage(),
				base.getAnnee(), 
				base.getEtablissement().getId()
		});
		if(nbRowsAffected == 0){
			nbRowsAffected = getJdbcTemplate().update(reqInsert, new Object[]{
					base.getAnnee(), 
					base.getEtablissement().getId(),
					structures.getParametres().getDateExport(),
					structures.getParametres().getHorodatage()
			});
		}
		
		if(nbRowsAffected != 1){
			throw new NullPointerException("Nombre de lignes mises à jour incorrect !");
		}
		*/
        
        getJdbcTemplate().update("DELETE FROM " + TABLE_MEF_STRUCTURE + " WHERE EXISTS(SELECT 1 FROM " + TABLE_MEF + " m WHERE m.mefid=mefid AND m.baseid=?)", new Object[]{
            base.getId()
        }); 
         
		boolean enregistrementStructuresOK = true;
		for(Division div : structures.getDivisions()){
			enregistrementStructuresOK &= saveDivision(base, structures, div);
		}
		for(Groupe grp : structures.getGroupes()){
			enregistrementStructuresOK &= saveGroupe(base, structures, grp);
		}
		
		return enregistrementStructuresOK;
	}

	private boolean saveDivision(BaseAnneeScolaire base, Structures structures, Division div) {
		/*
		int nbRowsAffected = getJdbcTemplate().update("INSERT INTO bee_division (annee, etablissementid, code, libelle_long, code_rne)"
				+ "										 VALUES(?, ?, ?, ?, ?)", new Object[]{
						base.getAnnee(), 
						base.getEtablissement().getId(),
						div.getCode(),
						div.getLibelle_long(),
						div.getCode_rne()
		});
		
		if(nbRowsAffected != 1){
			return false;
		}
		*/
        
        int nbRowsAffected = getJdbcTemplate().update("UPDATE " + TABLE_STRUCTURE + " SET libelle=? WHERE baseid=? AND id_siecle=? ", new Object[]{
           div.getLibelle_long(),
           base.getId(),
           div.getCode()
        });
        
        if(nbRowsAffected == 0){
            nbRowsAffected = getJdbcTemplate().update("INSERT INTO " + TABLE_STRUCTURE + " (baseid,libelle,id_siecle,type_structure) VALUES(?,?,?,?)", new Object[]{
               base.getId(),
               div.getLibelle_long(),
               div.getCode(),
               "CL"
            });
        }
        
        boolean enregistrementMefsOK = true;
        if(nbRowsAffected == 1){
            for(MEF mef : div.getMefsAppartenance()){
                enregistrementMefsOK &= saveAppartenanceMEF(base, structures, div, mef);
            }
            
        }else{
            return false;
        }
		
		return enregistrementMefsOK;
	}

	private boolean saveGroupe(BaseAnneeScolaire base, Structures structures, Groupe grp) {
		/*int nbRowsAffected = getJdbcTemplate().update("INSERT INTO bee_groupe (annee, etablissementid, code, libelle_long)"
				+ "										 VALUES(?, ?, ?, ?)", new Object[]{
						base.getAnnee(), 
						base.getEtablissement().getId(),
						grp.getCode(),
						grp.getLibelle_long(),
		});
		
		if(nbRowsAffected != 1){
			return false;
		}*/
        
        int nbRowsAffected = getJdbcTemplate().update("UPDATE " + TABLE_STRUCTURE + " SET libelle=? WHERE baseid=? AND id_siecle=? ", new Object[]{
           grp.getLibelle_long(),
           base.getId(),
           grp.getCode()
        });
		 
        if(nbRowsAffected == 0){
            nbRowsAffected = getJdbcTemplate().update("INSERT INTO " + TABLE_STRUCTURE + " (baseid,libelle,id_siecle,type_structure) VALUES(?,?,?,?) ", new Object[]{
               base.getId(),
               grp.getLibelle_long(),
               grp.getCode(),
               "GP"
            });
        }
        
        boolean enregistrementAppartenancesOK = true;
        if(nbRowsAffected == 1){
            for(MEF mef : grp.getMefsAppartenance()){
                enregistrementAppartenancesOK &= saveAppartenanceMEF(base, structures, grp, mef);
            }
            for(Division div : grp.getDivisionsAppartenances()){
                enregistrementAppartenancesOK &= saveAppartenanceDivision(base, structures, grp, div);
            }
            
        }else{
            return false;
        }
		return enregistrementAppartenancesOK;
	}

	private boolean saveAppartenanceMEF(BaseAnneeScolaire base, Structures structures, Division st, MEF mef) {
		
		/*int nbRowsAffected = getJdbcTemplate().update("INSERT INTO bee_structure_mef (annee, etablissementid, code_structure, type_structure, code_mef)"
				+ "									  VALUES(?, ?, ?, ?, ?)", new Object[]{
						base.getAnnee(), 
						base.getEtablissement().getId(),
						st.getCode(),
						(st instanceof Groupe) ? "GP" : "DI",
						mef.getCode()
				});
		*/
        int nbRowsAffected = getJdbcTemplate().update("INSERT INTO " + TABLE_MEF_STRUCTURE + " (mefid,structureid) VALUES(getMefByCodeSiecle(?,?),getStructureIdByIdSiecle(?,?))", new Object[]{
           base.getId(),
           mef.getCode(),
           base.getId(),
           st.getCode()
        });
        
		return nbRowsAffected == 1;
	}

	private boolean saveAppartenanceDivision(BaseAnneeScolaire base, Structures structures, Groupe grp, Division div) {
		/*
		int nbRowsAffected = getJdbcTemplate().update("INSERT INTO bee_division_groupe (annee, etablissementid, code_division, code_groupe)"
				+ "										VALUES(?, ?, ?, ?)", new Object[]{
						base.getAnnee(), 
						base.getEtablissement().getId(),
						div.getCode(), 
						grp.getCode()
				});
		*/
        /*int nbRowsAffected = getJdbcTemplate().update("UPDATE " + TABLE_STRUCTURE +  " SET structureidroot=getStructureIdByIdSiecle(?,?) WHERE baseid=? AND id_siecle=? ", new Object[]{
           base.getId(),
           div.getCode(),
           base.getId(),
           grp.getCode()
        });*/
        
        long groupeId = getJdbcTemplate().queryForObject("SELECT getStructureIdByIdSiecle(?,?)", new Object[]{
            base.getId(),
            grp.getCode()
        }, Long.class);
        
        if(groupeId == 0){
            log.error("Groupe '" + grp.getCode() + "' introuvable !");
        }
        
        long classeId = getJdbcTemplate().queryForObject("SELECT getStructureIdByIdSiecle(?,?)", new Object[]{
            base.getId(),
            div.getCode()
        }, Long.class);
        
        if(classeId == 0){
            log.error("Classe '" + div.getCode() + "' introuvable !");
        }
        
        if(groupeId == 0 || classeId == 0){
            return true;
        }
        
        int nbRowsAffected = getJdbcTemplate().update("INSERT INTO " + TABLE_STRUCTURE_GROUPE + " (groupeid, classeid) VALUES(?,?)", new Object[]{
            groupeId,
            classeId
        });
        
		return nbRowsAffected == 1;
	}
}
